package com.is.control;

import java.util.List;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import com.is.model.Homework;
import com.is.util.Pagination;
import com.is.dbconn.DBConnect;

public class HomeworkControl {
		public static Pagination pageInstance = null;
		
		public static boolean insertHomework(Homework d) {
			DBConnect dbConnect= new DBConnect();
			String sql= "insert into homework (stuId,timeId,level) values(?,?,?)";
			dbConnect.prepareStatement(sql);
			try {
				setValue(d, dbConnect);
				if(dbConnect.executeUpdate()){
					return true;
				}
			} catch (Exception e) {
			    e.printStackTrace();
			    System.out.println("insertDictaion error");
			}finally{
				dbConnect.close();
			}
			return false;
			
		}
		
		//事务提交听写结果
		public static boolean insertHomework(List<Homework> dics){
			DBConnect dbConnect = new DBConnect();
			String sql= "insert into homework (stuId,timeId,level) values(?,?,?)";
			PreparedStatement pstmt = null;
			dbConnect.setAutoCommit(false);
			dbConnect.prepareStatement(sql);
			pstmt = dbConnect.getPreparedStatement();
			try {
				for(Homework d: dics){
					int index = 1;
					pstmt.setInt(index++, d.getStuId());
					pstmt.setInt(index++, d.getTimeId());
					pstmt.setString(index++, d.getLevel());
					pstmt.addBatch();
				}
				pstmt.executeBatch();
				dbConnect.commit();
				dbConnect.setAutoCommit(true);
				return true;
			} catch (Exception e) {
				dbConnect.rollback();
				e.printStackTrace();
				System.out.println("insertHomework(List) ERROR");
				return false;
			}finally{
				dbConnect.close();
			}
		}
		
		public static boolean updateHomework(Homework d) {
			DBConnect dbConnect= new DBConnect();
			String sql= "update homework set stuId=?,timeId=?,level=? where id='"+d.getId()+"'";
			dbConnect.prepareStatement(sql);
			try {
				setValue(d, dbConnect);
				if(dbConnect.executeUpdate()){
					return true;
				}
			} catch (Exception e) {
			    e.printStackTrace();
			    System.out.println("update error");
			}finally{
				dbConnect.close();
			}
			return false;
			
		}
		public static boolean updateHomeworkByIdAndTimeId(int stuId,int timeId,String level){
			DBConnect dbc = new DBConnect();
			String sql = "update homework set level = '"+ level +"' where stuId='"+stuId+"' and timeId='"+timeId+"'";
			dbc.prepareStatement(sql);
			try{
				if(dbc.executeUpdate()){
					return true;
				}
			} catch (Exception e) {
				e.printStackTrace();
				System.out.println("updateHomeworkByIdAndTimeId ERROR");
			} finally{
				dbc.close();
			}
			return false;
		}
		public static void setValue(Homework d,DBConnect dbc){
			//调用此方法需注意字段顺序(与sql语句保持一致)
			int index=1;
			try{
			dbc.setInt(index++, d.getStuId());
			dbc.setInt(index++, d.getTimeId());	
			dbc.setString(index++, d.getLevel());
			
		}catch (Exception e) {
			e.printStackTrace();
			System.out.println("setValue方法出错");
		}
		}
		
		public static void getRs(Homework d,ResultSet rs){
			try {
				d.setId(rs.getInt("id"));
				d.setTimeId(rs.getInt("timeId"));
				d.setLevel(rs.getString("level"));
				d.setStuId(rs.getInt("stuId"));
			} catch (Exception e) {
				e.printStackTrace();
				System.out.println("getRs出错");
			}
		}
		
		//删除某一次听写的所有记录
		public static boolean deleteHomeworkByTimeId(int timeId){
			DBConnect dbConnect = new DBConnect();
			String sql = "delete from homework where timeId = '"+timeId+"' ";
			dbConnect.prepareStatement(sql);
			try {
				if(dbConnect.executeUpdate()){
					return true;
				}
			} catch (Exception e) {
				e.printStackTrace();
				System.out.println("deleteHomeworkByTimeId ERROR");
			}finally{
				dbConnect.close();
			}
			return false;
			}
		
		public static ArrayList<Homework> getHomeworkByTimeId(int timeId) {
			ArrayList<Homework> homeworks = new ArrayList<Homework>();
			DBConnect dbConnect = new DBConnect();
			String sql = "select * from homework where timeId='"+timeId+"'";
			dbConnect.prepareStatement(sql);
			ResultSet rSet=null;
			try {
				rSet = dbConnect.executeQuery();
				while(rSet.next()){
				    Homework homework = new Homework();
					getRs(homework, rSet);
					homeworks.add(homework);
				}
			} catch (Exception e) {
				e.printStackTrace();
				System.out.println("getDictationsByTimeId ERROR");
			}finally{
				dbConnect.close();
			}
			return homeworks;
		}
		
		
		/**
		 * 统计次数
		 * @param stuId
		 * @param level
		 * @param stuyear 
		 * @return
		 */
		
		public static int getCount(int stuId,String level,int courseClassId,int stuyear,String time1,String time2){
		    DBConnect dbConnect = new DBConnect();
		    String sTime = ParamStuyearControl.getStuyear(stuyear).getStartTime();
		    if(time1!=null&&!time1.equals("")){
		    	sTime = time1;
		    }
		    String eTime = ParamStuyearControl.getStuyear(stuyear).getEndTime();
		    if(time2!=null&&!time2.equals("")){
		    	eTime = time2;
		    }
		    int  count = 0;
		    String sql="select count(homework.id) from homework,homework_time where homework.timeId = homework_time.id " +
		    		" and (homework_time.time between '"+sTime+"' and '"+eTime+"') and homework.level like '"+level+"' " +
		    				" and homework_time.courseClassId='"+courseClassId+"' and stuId='"+stuId+"'";
		    dbConnect.prepareStatement(sql);
		    //System.out.println(sql);
		    ResultSet rSet = null;
		    try {
			rSet = dbConnect.executeQuery();
			if(rSet.next()){
			    count = rSet.getInt(1);
			}
		    } catch (Exception e) {
			e.printStackTrace();
			System.out.println("getCountByLevelAndStuId ERROR");
		    }finally{
			dbConnect.close();
		    }
		    return count;
		}
		
		
		
		/**
		 * 
		 * @param stuId
		 * @param courseId
		 * @param time
		 * @param page
		 * @param rowsPerPage
		 * @return
		 * @throws SQLException
		 * @throws Exception
		 */
		public static Collection<Object> getHomeworksByMuti(String stuId,String courseId,String time,int page, int rowsPerPage) throws SQLException, Exception {
			String sql="";
			if(stuId==null||stuId.equals("")){
				stuId="%";
			}
			if(courseId==null||courseId.equals("")){
				courseId="%";
			}
			if(time==null||time.equals("")){
				time = "%";
			}
			sql = "select * from homework where stuId like '"+stuId+"' and courseId ='"+courseId+"' and time like '"+time+"' ";
			pageInstance = new HomeworkControlPage(sql, rowsPerPage);
			return pageInstance.getPage(page);
		}
	      
	      
	}
	class HomeworkControlPage extends Pagination {
	           public HomeworkControlPage(String sql, int rowsPerPage) {
	               super(sql, rowsPerPage, 1);
	}

	        protected Collection<Homework> packResultSet(ResultSet rs) throws SQLException {
		Collection<Homework> ds = new ArrayList<Homework>();
		Homework d = null;
		while (rs.next()) {
			d=new Homework();
			d.setId(rs.getInt("id"));
			d.setTimeId(rs.getInt("timeId"));
			d.setLevel(rs.getString("level"));
			d.setStuId(rs.getInt("stuId"));
		        ds.add(d);
			d = null;
		}
		return ds;
	}

	}



